package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAsset;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRDepartment;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;
import extend.org.compiere.utils.Util;

public class A_Asset_Form_DA4 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	Integer A_InternalMovement_ID, p_Accountant_ID;
	
	Timestamp p_DatePreparation, p_DateActBegin, p_DateActEnd, p_DateAcct;
	
	String ResultPath, p_DocumentNo;
	
	@Override
	protected void prepare() {
		log.info( "");
		m_ctx = Env. getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("DocumentNo"))
				p_DocumentNo = (String) para[i].getParameter();
			else if (name.equals("dateAct")) {
				p_DateActBegin = (Timestamp) para[i].getParameter();
				p_DateActEnd = (Timestamp) para[i].getParameter_To();
			}
			else if (name.equals("Accountant_ID"))
				p_Accountant_ID = para[i].getParameterAsInt();
			else if (name.equals("DateAcct"))
				p_DateAcct = (Timestamp) para[i].getParameter();
			else
				log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_D4_";
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(57).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		//sender
		String sqlID = "SELECT min(A_InternalMovement_ID) FROM A_InternalMovement aim WHERE aim.DocumentNo = '" + p_DocumentNo + "'";
		A_InternalMovement_ID = DB.getSQLValue(get_TrxName(), sqlID);
		String sqlMOLSender = "select aim.c_bpartnersr_id from a_internalmovement aim "
				+ " where a_asset_id = (select a_asset_id from a_internalmovement aim " 
											+ " where aim.a_internalmovement_id = " + A_InternalMovement_ID + ") "
				+ " and datemoved = (select max(datemoved) from a_internalmovement aim "  
										+ " where a_asset_id = (select a_asset_id from a_internalmovement aim " 
																	+ " where aim.a_internalmovement_id = " + A_InternalMovement_ID + ") "
										+ " and datemoved < (select datemoved from a_internalmovement aim " 
																+ " where aim.a_internalmovement_id = " + A_InternalMovement_ID + "))";
		int idMOLSender = DB.getSQLValue(get_TrxName(), sqlMOLSender);
		String sqlDepartment = "SELECT HR_Department_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + idMOLSender;
		int idDepartment = DB.getSQLValue(get_TrxName(), sqlDepartment);
		sheet.getRow(11).getCell(6).setCellValue((new MHRDepartment(m_ctx, idDepartment, get_TrxName()).getName()));
		
		//Recipient
		String sqlMOLRecipient = "SELECT C_BPartnersr_ID FROM A_InternalMovement aim WHERE aim.A_InternalMovement_ID = " + A_InternalMovement_ID;
		int idMOLRecipient = DB.getSQLValue(get_TrxName(), sqlMOLRecipient);
		sqlDepartment = "SELECT HR_Department_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + idMOLRecipient;
		idDepartment = DB.getSQLValue(get_TrxName(), sqlDepartment);
		sheet.getRow(13).getCell(6).setCellValue((new MHRDepartment(m_ctx, idDepartment, get_TrxName()).getName()));
		
		//DocumentBase
		String sqlDoc = "SELECT hro.Name FROM HRM_Order hro WHERE hro.HRM_Number = '" + p_DocumentNo + "'";
		String DocName = DB.getSQLValueString(get_TrxName(), sqlDoc);
		sheet.getRow(22).getCell(16).setCellValue(DocName);																		//"приказ " + docNo);
		
		sqlDoc = "SELECT hro.DateFrom FROM HRM_Order hro WHERE hro.HRM_Number = '" + p_DocumentNo + "'";
		Timestamp docDateFrom = DB.getSQLValueTS(get_TrxName(), sqlDoc);
		sheet.getRow(23).getCell(25).setCellValue(p_DocumentNo);
		sheet.getRow(23).getCell(43).setCellValue((new SimpleDateFormat("dd").format(docDateFrom)));
		sheet.getRow(23).getCell(46).setCellValue((new SimpleDateFormat("MMMMM").format(docDateFrom)));
		sheet.getRow(23).getCell(52).setCellValue((new SimpleDateFormat("yyyy").format(docDateFrom)));
		
		//Passed
		String sqlJob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + idMOLSender;
		int idJob = DB.getSQLValue(get_TrxName(), sqlJob);
		sheet.getRow(46).getCell(4).setCellValue((new MHRJob(m_ctx, idJob, get_TrxName()).getName()));
		sheet.getRow(46).getCell(23).setCellValue((new MBPartner(m_ctx, idMOLSender, get_TrxName()).getName()));
		sheet.getRow(46).getCell(40).setCellValue((new SimpleDateFormat("dd").format(p_DateActBegin)));
		sheet.getRow(46).getCell(43).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateActBegin)));
		sheet.getRow(46).getCell(49).setCellValue((new SimpleDateFormat("yyyy").format(p_DateActBegin)));
		
		//Accepted
		sqlJob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + idMOLRecipient;
		idJob = DB.getSQLValue(get_TrxName(), sqlJob);
		sheet.getRow(49).getCell(4).setCellValue((new MHRJob(m_ctx, idJob, get_TrxName()).getName()));
		sheet.getRow(49).getCell(23).setCellValue((new MBPartner(m_ctx, idMOLRecipient, get_TrxName()).getName()));
		sheet.getRow(49).getCell(40).setCellValue((new SimpleDateFormat("dd").format(p_DateActEnd)));
		sheet.getRow(49).getCell(43).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateActEnd)));
		sheet.getRow(49).getCell(49).setCellValue((new SimpleDateFormat("yyyy").format(p_DateActEnd)));
		
		//Accountant
		sqlJob = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_Accountant_ID;
		idJob = DB.getSQLValue(get_TrxName(), sqlJob);
		sheet.getRow(54).getCell(11).setCellValue((new MHRJob(m_ctx, idJob, get_TrxName()).getName()));
		sheet.getRow(54).getCell(30).setCellValue((new MBPartner(m_ctx, p_Accountant_ID, get_TrxName()).getName()));
		sheet.getRow(54).getCell(47).setCellValue((new SimpleDateFormat("dd").format(p_DateAcct)));
		sheet.getRow(54).getCell(50).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateAcct)));
		sheet.getRow(54).getCell(56).setCellValue((new SimpleDateFormat("yyyy").format(p_DateAcct)));

		//Positions
		int row = 31, no = 0, totalQty = 0;
		double totalAmt = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sqlMove = "SELECT * FROM A_InternalMovement aim WHERE aim.DocumentNo = '" + p_DocumentNo + "'";
		try {
			 ps = DB.prepareStatement(sqlMove.toString(), get_TrxName());
			 rs = ps.executeQuery();

			 while (rs.next()) {
				//Shifting and Merging
				sheet.shiftRows(row, row + 30, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 18));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 19, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 33));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 38));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 39, 44));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 45, 47));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 48, 52));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 53, 57));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 58, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(++no);
				MAsset asset = new MAsset(m_ctx, rs.getInt("A_Asset_ID"));
				sheet.getRow(row).getCell(3).setCellValue(asset.getName());
				sheet.getRow(row).getCell(19).setCellValue(asset.getA_Asset_CreateDate() == null ? "" : (new SimpleDateFormat("dd.MM.yyyy").format(asset.getA_Asset_CreateDate())));
				sheet.getRow(row).getCell(28).setCellValue(asset.getValue());
				sheet.getRow(row).getCell(34).setCellValue(asset.getSerNo());
//				sheet.getRow(row).getCell(39).setCellValue();
				sheet.getRow(row).getCell(45).setCellValue("шт");
				sheet.getRow(row).getCell(48).setCellValue(asset == null ? "" : "1");
				sheet.getRow(row).getCell(53).setCellValue(asset.getAssetCost(p_DateAcct).doubleValue());
				sheet.getRow(row).getCell(58).setCellValue(asset.getAssetCost(p_DateAcct).doubleValue());
				
				totalQty++;
				totalAmt += asset.getAssetCost(p_DateAcct).doubleValue();
				
				row++;
			}
			 
			sheet.getRow(row + 2).getCell(17).setCellValue(AnalogConvertor_RU.toString(totalQty));
			sheet.getRow(row + 2).getCell(43).setCellValue(AnalogConvertor_RU.toString(totalAmt));
			
		} catch (Exception e) {
			log.log(Level.SEVERE, sqlMove, e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
					
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	

}
